﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class TopUp
    {
        public TopUp()
        {
        }

        public int GetRowNumber(string value)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT count(*) AS jmlData FROM(
                                                     SELECT * FROM TBL_TOPUP (NOLOCK) WHERE LTRIM(RTRIM(BATCH_ID))='' AND (CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%')
                                                     ) AS DATA");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<TopUpInfo> GetList(string value, int currPage, int dataPerPage)
        {
            List<TopUpInfo> list = new List<TopUpInfo>();
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT  * FROM ( 
                         SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, * 
                         FROM TBL_TOPUP (NOLOCK) WHERE (CIF_NUMBER LIKE @PARAM) OR (DEBITOR_NAME LIKE @PARAM) 
                         )AS LogWithRowNumbers 
                         WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + value + "%");
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), CharToBool(dr.GetString(28)), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetString(37)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpInfo> GetList(string value)
        {
            List<TopUpInfo> list = new List<TopUpInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP WHERE (CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), CharToBool(dr.GetString(27)), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpInfo> GetListByBranch(string BranchID)
        {
            List<TopUpInfo> list = new List<TopUpInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP WHERE UNIT_CODE LIKE '' + @BRANCH_ID + '' ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), CharToBool(dr.GetString(27)), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpInfo> GetListApproval(string value, string UpdateBy)
        {
            List<TopUpInfo> list = new List<TopUpInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP_TMP WHERE ((CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%')) AND (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND LTRIM(RTRIM(BATCH_ID))='') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), CharToBool(dr.GetString(27)), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpInfo> GetListApproval(string UpdateBy)
        {
            List<TopUpInfo> list = new List<TopUpInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP_TMP WHERE (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND IS_MAIN_DELETED = '1') ORDER BY UPDATE_DATE DESC");
            // ((CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%')) AND
            //db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), CharToBool(dr.GetString(27)), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpInfo> GetListApproval(string UpdateBy, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<TopUpInfo> list = new List<TopUpInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(
                                @"SELECT  * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, * 
                                    FROM TBL_TOPUP_TMP (NOLOCK) 
                                    WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND IS_MAIN_DELETED = '1'
                                )AS LogWithRowNumbers 
                                    WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR");
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), CharToBool(dr.GetString(28)), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36)));
                }
                dr.Close();
            }
            return list;
        }

        public int GetListApprovalNumber(string UpdateBy)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT count(*) AS jmlData FROM ( 
                                SELECT * FROM TBL_TOPUP_TMP (NOLOCK)
                                WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND IS_MAIN_DELETED = '1') as Data
                                ");
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            //AND LTRIM(RTRIM(BATCH_ID))='' 
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<TopUpInfo> GetListAssignment(string NIP, string VALUE)
        {
            List<TopUpInfo> list = new List<TopUpInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS 

            SELECT CIF_NUMBER,LOAN_NUMBER,UNIT_CODE,DEBITOR_NAME
                ,PRODUCT_CODE,ISNULL(CURRENT_OS,0) AS CURRENT_OS, ISNULL(CURRENT_INSTALLMENT,0) AS CURRENT_INSTALLMENT 
                ,ISNULL(OFFER_PLAFON,0) AS OFFER_PLAFON
                ,ISNULL(OFFER_INSTALLMENT,0) AS OFFER_INSTALLMENT,ISNULL(OFFER_INTEREST,0) AS OFFER_INTEREST
                ,ISNULL(OFFER_TENOR,0) AS OFFER_TENOR,ISNULL(OFFER_FRESH_MONEY,0) AS OFFER_FRESH_MONEY,PRIORITY,JENIS_USAHA
                ,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS,KELURAHAN_BUSINESS,ZIP_CODE_BUSINESS
                ,ADDRESS_BUSINESS, MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE, DATE_VISIT_PLAN
                ,ISNULL(B.MS_STD_NAME,'') AS PROSPECT_TYPE,ISNULL(C.MS_STD_NAME,'') AS DEBTOR_TYPE
                ,ISNULL(D.CHANNEL_NAME,'') AS CHANNELING,EKSTERNAL,REFERENSI
                ,B.MS_STD_ID AS PROSPECT_TYPE_ID, C.MS_STD_ID AS DEBTOR_TYPE_ID,D.CHANNEL_ID
             FROM TBL_TOPUP 
                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS B ON TBL_TOPUP.PROSPECT_TYPE = B.MS_STD_ID
                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS C ON TBL_TOPUP.DEBTOR_TYPE = C.MS_STD_ID
                LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS D ON TBL_TOPUP.CHANNELING = D.CHANNEL_ID
            WHERE TBL_TOPUP.IS_ASSIGNED = '0' AND TBL_TOPUP.UNIT_CODE IN 
                    (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID)
                AND (TBL_TOPUP.CIF_NUMBER LIKE @PARAM OR TBL_TOPUP.DEBITOR_NAME LIKE @PARAM) 
                ORDER BY TBL_TOPUP.UNIT_CODE, TBL_TOPUP.UPDATE_DATE DESC");

            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + VALUE + "%");
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetString(24), dr.GetString(25), dr.GetString(26), dr.GetString(27)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpInfo> GetListAssignSOPaging(string NIP, string VALUE, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<TopUpInfo> list = new List<TopUpInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"
            DECLARE @PROCESS_DATE DATETIME
            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS

            SELECT * FROM ( 
                SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, 
                CIF_NUMBER,LOAN_NUMBER,UNIT_CODE,DEBITOR_NAME
                ,PRODUCT_CODE,ISNULL(CURRENT_OS,0) AS CURRENT_OS, ISNULL(CURRENT_INSTALLMENT,0) AS CURRENT_INSTALLMENT 
                ,ISNULL(OFFER_PLAFON,0) AS OFFER_PLAFON
                ,ISNULL(OFFER_INSTALLMENT,0) AS OFFER_INSTALLMENT,ISNULL(OFFER_INTEREST,0) AS OFFER_INTEREST
                ,ISNULL(OFFER_TENOR,0) AS OFFER_TENOR,ISNULL(OFFER_FRESH_MONEY,0) AS OFFER_FRESH_MONEY,PRIORITY,JENIS_USAHA
                ,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS,KELURAHAN_BUSINESS,ZIP_CODE_BUSINESS
                ,ADDRESS_BUSINESS, MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE, DATE_VISIT_PLAN
                ,ISNULL(B.MS_STD_NAME,'') AS PROSPECT_TYPE,ISNULL(C.MS_STD_NAME,'') AS DEBTOR_TYPE
                ,ISNULL(D.CHANNEL_NAME,'') AS CHANNELING,EKSTERNAL,REFERENSI
                ,B.MS_STD_ID AS PROSPECT_TYPE_ID, C.MS_STD_ID AS DEBTOR_TYPE_ID,D.CHANNEL_ID
             FROM TBL_TOPUP 
                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS B ON TBL_TOPUP.PROSPECT_TYPE = B.MS_STD_ID
                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS C ON TBL_TOPUP.DEBTOR_TYPE = C.MS_STD_ID
                LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS D ON TBL_TOPUP.CHANNELING = D.CHANNEL_ID
             WHERE TBL_TOPUP.IS_ASSIGNED = '0' AND TBL_TOPUP.UNIT_CODE IN 
                    (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID)
                AND (TBL_TOPUP.CIF_NUMBER LIKE @PARAM OR TBL_TOPUP.DEBITOR_NAME LIKE @PARAM) 
                        ) AS LogWithRowNumbers 
                   WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + VALUE + "%");
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetString(25), dr.GetString(26), dr.GetString(27), dr.GetString(28)));
                }
                dr.Close();
            }
            return list;
        }

        public int GetRowNumberAssignSO(string NIP, string VALUE)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"DECLARE @PROCESS_DATE DATETIME
            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS 

            SELECT count(1) AS jmlData FROM( 
            SELECT CIF_NUMBER,LOAN_NUMBER,UNIT_CODE,DEBITOR_NAME
                ,PRODUCT_CODE,ISNULL(CURRENT_OS,0) AS CURRENT_OS, ISNULL(CURRENT_INSTALLMENT,0) AS CURRENT_INSTALLMENT 
                ,ISNULL(OFFER_PLAFON,0) AS OFFER_PLAFON
                ,ISNULL(OFFER_INSTALLMENT,0) AS OFFER_INSTALLMENT,ISNULL(OFFER_INTEREST,0) AS OFFER_INTEREST
                ,ISNULL(OFFER_TENOR,0) AS OFFER_TENOR,ISNULL(OFFER_FRESH_MONEY,0) AS OFFER_FRESH_MONEY,PRIORITY,JENIS_USAHA
                ,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS,KELURAHAN_BUSINESS,ZIP_CODE_BUSINESS
                ,ADDRESS_BUSINESS, MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE, DATE_VISIT_PLAN
                ,ISNULL(B.MS_STD_NAME,'') AS PROSPECT_TYPE,ISNULL(C.MS_STD_NAME,'') AS DEBTOR_TYPE
                ,ISNULL(D.CHANNEL_NAME,'') AS CHANNELING,EKSTERNAL,REFERENSI
                ,B.MS_STD_ID AS PROSPECT_TYPE_ID, C.MS_STD_ID AS DEBTOR_TYPE_ID,D.CHANNEL_ID
             FROM TBL_TOPUP 
                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 5) AS B ON TBL_TOPUP.PROSPECT_TYPE = B.MS_STD_ID
                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 9) AS C ON TBL_TOPUP.DEBTOR_TYPE = C.MS_STD_ID
                LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL) AS D ON TBL_TOPUP.CHANNELING = D.CHANNEL_ID
            WHERE TBL_TOPUP.IS_ASSIGNED = '0' AND TBL_TOPUP.UNIT_CODE IN 
                    (SELECT BRANCH_ID FROM V_USER WHERE NIP = @NIP GROUP BY BRANCH_ID
                            UNION
                        SELECT BRANCH_ID FROM TBL_ALTERNATE_UM 
                            WHERE NIP = @NIP
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                            AND IS_ACTIVE = '1' AND ALTERNATE_FOR = 'UM' GROUP BY BRANCH_ID)
                AND (TBL_TOPUP.CIF_NUMBER LIKE @PARAM OR TBL_TOPUP.DEBITOR_NAME LIKE @PARAM) 
                ) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + VALUE + "%");
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public bool IsExist(string CIF_NUMBER, string LOAN_NUMBER)
        {
            bool result = false;
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 CIF_NUMBER FROM TBL_TOPUP NOLOCK
            WHERE LTRIM(RTRIM(CIF_NUMBER)) = @CIF_NUMBER
            AND LTRIM(RTRIM(LOAN_NUMBER)) = @LOAN_NUMBER
            UNION 
            SELECT TOP 1 CIF_NUMBER FROM TBL_TOPUP_TMP NOLOCK
            WHERE LTRIM(RTRIM(CIF_NUMBER)) = @CIF_NUMBER
            AND LTRIM(RTRIM(LOAN_NUMBER)) = @LOAN_NUMBER");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER == null ? "" : CIF_NUMBER.Trim());
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER == null ? "" : LOAN_NUMBER.Trim());
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    result = true;
                }
                dr.Close();
            }
            return result;
        }

        public TopUpInfo Get(string CIF_NUMBER, string LOAN_NUMBER)
        {
            TopUpInfo ci = new TopUpInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP WHERE LTRIM(RTRIM(CIF_NUMBER)) LIKE '' + @CIF_NUMBER + '' AND LTRIM(RTRIM(LOAN_NUMBER)) LIKE '' + @LOAN_NUMBER + ''");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new TopUpInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), CharToBool(dr.GetString(27)), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36));
                }
                dr.Close();
            }
            return ci;
        }

        public TopUpInfo GetApprovalByID(string CIF_NUMBER, string LOAN_NUMBER)
        {
            TopUpInfo ci = new TopUpInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP_TMP WHERE LTRIM(RTRIM(CIF_NUMBER)) LIKE '' + @CIF_NUMBER + '' AND LTRIM(RTRIM(LOAN_NUMBER)) LIKE '' + @LOAN_NUMBER + '' AND IS_MAIN_DELETED = '1'");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new TopUpInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), CharToBool(dr.GetString(27)), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35));
                }
                dr.Close();
            }
            return ci;
        }

        public int Save(TopUpInfo TBL_TOPUP_TMP)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_TOPUP_TMP SET CIF_NUMBER = LEFT(@CIF_NUMBER,8),LOAN_NUMBER = LEFT(@LOAN_NUMBER,15),
                    UNIT_CODE = LEFT(@UNIT_CODE,6),DEBITOR_NAME = LEFT(@DEBITOR_NAME,50),PRODUCT_CODE = LEFT(@PRODUCT_CODE,5),
                    CURRENT_OS = @CURRENT_OS,CURRENT_INSTALLMENT = @CURRENT_INSTALLMENT,
                    OFFER_PLAFON = @OFFER_PLAFON,OFFER_INSTALLMENT = @OFFER_INSTALLMENT,OFFER_INTEREST = LEFT(@OFFER_INTEREST,5),
                    OFFER_TENOR = @OFFER_TENOR,OFFER_FRESH_MONEY = @OFFER_FRESH_MONEY,PRIORITY = LEFT(@PRIORITY,20),
                    JENIS_USAHA = LEFT(@JENIS_USAHA,100),
                    KODYA_KAB_BUSINESS = LEFT(@KODYA_KAB_BUSINESS,50),KECAMATAN_BUSINESS = LEFT(@KECAMATAN_BUSINESS,50),
                    KELURAHAN_BUSINESS = LEFT(@KELURAHAN_BUSINESS,50),ZIP_CODE_BUSINESS = LEFT(@ZIP_CODE_BUSINESS,6),ADDRESS_BUSINESS = LEFT(@ADDRESS_BUSINESS,255),
                    MOBILE_PHONE = LEFT(@MOBILE_PHONE,18),OFFICE_PHONE = RIGHT(@OFFICE_PHONE,12),RESIDENTIAL_PHONE = RIGHT(@RESIDENTIAL_PHONE,12),
                    DATE_VISIT_PLAN = @DATE_VISIT_PLAN,
                    ENTRY_BY = @ENTRY_BY,ENTRY_DATE = @ENTRY_DATE,
                    UPDATE_BY = @UPDATE_BY,UPDATE_DATE = @UPDATE_DATE, ACT_TYPE = @ACT_TYPE,
                    PROSPECT_TYPE = @PROSPECT_TYPE, DEBTOR_TYPE = @DEBTOR_TYPE, CHANNELING = @CHANNELING, [EKSTERNAL] = @EKSTERNAL, REFERENSI = @REFERENSI, IS_MAIN_DELETED = '1'
                WHERE (LTRIM(RTRIM(CIF_NUMBER)) = @CIF_NUMBER AND LTRIM(RTRIM(LOAN_NUMBER)) = @LOAN_NUMBER);
                IF @@ROWCOUNT = 0
                INSERT INTO TBL_TOPUP_TMP
                (
                    CIF_NUMBER,LOAN_NUMBER,UNIT_CODE,DEBITOR_NAME,PRODUCT_CODE,CURRENT_OS,CURRENT_INSTALLMENT,
                    OFFER_PLAFON,OFFER_INSTALLMENT,OFFER_INTEREST,OFFER_TENOR,OFFER_FRESH_MONEY,
                    PRIORITY,JENIS_USAHA,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS,KELURAHAN_BUSINESS,ZIP_CODE_BUSINESS,ADDRESS_BUSINESS,
                    MOBILE_PHONE,OFFICE_PHONE,RESIDENTIAL_PHONE,DATE_VISIT_PLAN,
                    ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE,
                    PROSPECT_TYPE,DEBTOR_TYPE,CHANNELING,[EKSTERNAL],REFERENSI, IS_MAIN_DELETED
                ) 
                VALUES
                (
                    LEFT(@CIF_NUMBER,8),LEFT(@LOAN_NUMBER,15),LEFT(@UNIT_CODE,6),LEFT(@DEBITOR_NAME,50),LEFT(@PRODUCT_CODE,5),@CURRENT_OS,@CURRENT_INSTALLMENT,
                    @OFFER_PLAFON,@OFFER_INSTALLMENT,LEFT(@OFFER_INTEREST,5),@OFFER_TENOR,@OFFER_FRESH_MONEY,
                    LEFT(@PRIORITY,20),LEFT(@JENIS_USAHA,100), LEFT(@KODYA_KAB_BUSINESS,50),LEFT(@KECAMATAN_BUSINESS,50),LEFT(@KELURAHAN_BUSINESS,50),LEFT(@ZIP_CODE_BUSINESS,6),LEFT(@ADDRESS_BUSINESS,255),
                    LEFT(@MOBILE_PHONE,18),RIGHT(@OFFICE_PHONE,12),RIGHT(@RESIDENTIAL_PHONE,12),@DATE_VISIT_PLAN,
                    @ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE,@ACT_TYPE,
                    LEFT(@PROSPECT_TYPE,10),LEFT(@DEBTOR_TYPE,10),LEFT(@CHANNELING,10),@EKSTERNAL,@REFERENSI, '1'
                );");
                db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, TBL_TOPUP_TMP.CIF_NUMBER);
                db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, TBL_TOPUP_TMP.LOAN_NUMBER);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, TBL_TOPUP_TMP.UNIT_CODE);
                db.AddInParameter(cmd, "@DEBITOR_NAME", DbType.String, TBL_TOPUP_TMP.DEBITOR_NAME);
                db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, TBL_TOPUP_TMP.PRODUCT_CODE);
                db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, TBL_TOPUP_TMP.CURRENT_OS);
                db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, TBL_TOPUP_TMP.CURRENT_INSTALLMENT);
                db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, TBL_TOPUP_TMP.OFFER_PLAFON);
                db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, TBL_TOPUP_TMP.OFFER_INSTALLMENT);
                db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, TBL_TOPUP_TMP.OFFER_INTEREST);
                db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, TBL_TOPUP_TMP.OFFER_TENOR);
                db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, TBL_TOPUP_TMP.OFFER_FRESH_MONEY);
                db.AddInParameter(cmd, "@PRIORITY", DbType.String, TBL_TOPUP_TMP.PRIORITY);
                db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, TBL_TOPUP_TMP.JENIS_USAHA);
                db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, TBL_TOPUP_TMP.KODYA_KAB_BUSINESS);
                db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, TBL_TOPUP_TMP.KECAMATAN_BUSINESS);
                db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, TBL_TOPUP_TMP.KELURAHAN_BUSINESS);
                db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, TBL_TOPUP_TMP.ZIP_CODE_BUSINESS);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, TBL_TOPUP_TMP.ADDRESS_BUSINESS);
                db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, TBL_TOPUP_TMP.MOBILE_PHONE);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, TBL_TOPUP_TMP.OFFICE_PHONE);
                db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, TBL_TOPUP_TMP.RESIDENTIAL_PHONE);
                db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime, TBL_TOPUP_TMP.DATE_VISIT_PLAN);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_TOPUP_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_TOPUP_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_TOPUP_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_TOPUP_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, TBL_TOPUP_TMP.ACT_TYPE);
                db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, TBL_TOPUP_TMP.PROSPECT_TYPE);
                db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, TBL_TOPUP_TMP.DEBTOR_TYPE);
                db.AddInParameter(cmd, "@CHANNELING", DbType.String, TBL_TOPUP_TMP.CHANNELING);
                db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, TBL_TOPUP_TMP.EKSTERNAL);
                db.AddInParameter(cmd, "@REFERENSI", DbType.String, TBL_TOPUP_TMP.REFERENSI);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex) { }
            return status;
        }

        public int UpdateAssigned(string CIF_NUMBER, string LOAN_NUMBER, string ASSIGNED_TO, string UPDATE_BY)
        {
            int l_result = 0;

            Database db = DBConnection.CreateDatabase();
            AppParam appParam = new AppParam();
            AppParamInfo parameter = appParam.Get();
            DateTime processTime = parameter.PROCESS_DATE;

            DbConnection connection = db.CreateConnection();
            connection.Open();
            DbTransaction dbTran = connection.BeginTransaction();

            try
            {
                DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_TOPUP SET IS_ASSIGNED = '1', IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE, ASSIGNED_TO = @ASSIGNED_TO, ASSIGNED_DATE = @ASSIGNED_DATE WHERE CIF_NUMBER=@CIF_NUMBER AND LOAN_NUMBER=@LOAN_NUMBER");
                db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
                db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, ASSIGNED_TO);
                db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, ProcessTime(processTime));
                db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, UPDATE_BY);
                db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, ProcessTime(processTime));

                if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                {
                    cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_TOPUP_DOWNLOAD WHERE CIF_NUMBER=@CIF_NUMBER AND LOAN_NUMBER=@LOAN_NUMBER");
                    db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                    db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
                    db.ExecuteNonQuery(cmd, dbTran);

                    cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_TOPUP (NOLOCK) WHERE CIF_NUMBER=@CIF_NUMBER AND LOAN_NUMBER=@LOAN_NUMBER");
                    db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                    db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);

                    DataSet ds = db.ExecuteDataSet(cmd, dbTran);
                    if (ValidateDataSetEmpty(ds))
                    {
                        DataRow dr = ds.Tables[0].Rows[0];

                        Branch branch = new Branch();
                        Holiday holiday = new Holiday();
                        List<DateTime> holidayList = holiday.GetHoliday();
                        MsStd std = new MsStd();
                        List<Shuba.Model.MstStdInfo> stdInfo = std.GetList(dr["JENIS_USAHA"].ToString(), 14);

                        int guFup = Convert.ToInt32(parameter.GU_FUP);
                        string AssignedTo = dr["ASSIGNED_TO"].ToString().Trim().Length < 5 ? dr["ASSIGNED_TO"].ToString().Trim().PadRight(5, 'X') : dr["ASSIGNED_TO"].ToString().Trim();
                        string SourceCode = branch.Get(Convert.ToString(dr["UNIT_CODE"])).SourceCode;
                        DateTime dateFollowUp = AddWorkDays(processTime, guFup, holidayList);
                        string jenisUsaha = "";
                        if (stdInfo.Count > 0)
                        {
                            jenisUsaha = stdInfo[0].MstID;
                        }

                        string address = dr["ADDRESS_BUSINESS"].ToString().Trim();
                        string prospect_id = dr["UNIT_CODE"].ToString().Trim() + AssignedTo + ProcessTime(processTime).ToString("yyMMddHHmmss");
                        if (prospect_id.Trim().Length < 21) prospect_id = prospect_id.Trim().PadRight(21, 'X');
                        if (prospect_id.Trim().Length > 21) prospect_id = prospect_id.Trim().Substring(0, 21);
                        if (address.Trim().Length > 255) address = address.Substring(0, 255);

                        Shuba.Model.ProspectInfo prospect = new Shuba.Model.ProspectInfo
                        {
                            PROSPECT_ID = prospect_id.Trim().ToUpper(),
                            PROSPECT_NO = "n/a",
                            AO_CODE = dr["ASSIGNED_TO"].ToString().Trim(),
                            UNIT_CODE = dr["UNIT_CODE"].ToString(),
                            SOURCE_CODE = SourceCode,
                            FULL_NAME = dr["DEBITOR_NAME"].ToString(),
                            CIF_NUMBER = dr["CIF_NUMBER"].ToString() == string.Empty ? 0 : int.Parse(dr["CIF_NUMBER"].ToString()),
                            LOAN_NUMBER = dr["LOAN_NUMBER"].ToString(),
                            PRODUCT_CODE = dr["PRODUCT_CODE"].ToString(),
                            CURRENT_OS = dr["CURRENT_OS"].ToString() == string.Empty ? 0 : float.Parse(dr["CURRENT_OS"].ToString()),
                            CURRENT_INSTALLMENT = dr["CURRENT_INSTALLMENT"].ToString() == string.Empty ? 0 : float.Parse(dr["CURRENT_INSTALLMENT"].ToString()),
                            OFFER_PLAFON = dr["OFFER_PLAFON"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_PLAFON"].ToString()),
                            RENCANA_PLAFON = dr["OFFER_PLAFON"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_PLAFON"].ToString()),
                            OFFER_INSTALLMENT = dr["OFFER_INSTALLMENT"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_INSTALLMENT"].ToString()),
                            OFFER_INTEREST = dr["OFFER_INTEREST"].ToString(),
                            OFFER_TENOR = dr["OFFER_TENOR"].ToString() == string.Empty ? Convert.ToInt16(0) : Int16.Parse(dr["OFFER_TENOR"].ToString()),
                            OFFER_FRESH_MONEY = dr["OFFER_FRESH_MONEY"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_FRESH_MONEY"].ToString()),
                            PRIORITY = dr["PRIORITY"].ToString(),
                            JENIS_USAHA = jenisUsaha,
                            KODYA_KAB_BUSINESS = dr["KODYA_KAB_BUSINESS"].ToString(),
                            KECAMATAN_BUSINESS = dr["KECAMATAN_BUSINESS"].ToString(),
                            KELURAHAN_BUSINESS = dr["KELURAHAN_BUSINESS"].ToString(),
                            ZIP_CODE_BUSINESS = dr["ZIP_CODE_BUSINESS"].ToString(),
                            ADDRESS_BUSINESS = address,
                            MOBILE_PHONE = dr["MOBILE_PHONE"].ToString(),
                            OFFICE_PHONE = dr["OFFICE_PHONE"].ToString(),
                            RESIDENTIAL_PHONE = dr["RESIDENTIAL_PHONE"].ToString(),
                            PROGRESS_STATUS = "NEW",
                            ENTRY_BY = UPDATE_BY,
                            ENTRY_DATE = ProcessTime(processTime),
                            GU_FUP = dateFollowUp,
                            PROSPECT_TYPE = dr["PROSPECT_TYPE"].ToString(),
                            DEBTOR_TYPE = dr["DEBTOR_TYPE"].ToString(),
                            CHANNELING = dr["CHANNELING"].ToString(),
                            EKSTERNAL = dr["EKSTERNAL"].ToString(),
                            REFERENSI = dr["REFERENSI"].ToString(),
                            ALTERNATE_FOR = "",

                            FLAG_FREEZE = Int16.Parse(dr["FLAG_FREEZE"].ToString())
                        };

                        cmd = db.GetSqlStringCommand(@"INSERT INTO TBL_PROSPECT ([PROSPECT_ID], [PROSPECT_NO], [UNIT_CODE], [SOURCE_CODE], [AO_CODE], [PROGRESS_STATUS], [CIF_NUMBER], 
                                                        [LOAN_NUMBER], [FULL_NAME], [PRODUCT_CODE], [CURRENT_OS], 
                                                        [CURRENT_INSTALLMENT], [RENCANA_PLAFON], [OFFER_PLAFON],
                                                        [OFFER_INSTALLMENT], [OFFER_INTEREST], [OFFER_TENOR], 
                                                        [OFFER_FRESH_MONEY], [PRIORITY], [KODYA_KAB_BUSINESS],
                                                        [KECAMATAN_BUSINESS], [KELURAHAN_BUSINESS], [ZIP_CODE_BUSINESS],
                                                        [ADDRESS_BUSINESS], [MOBILE_PHONE], [OFFICE_PHONE], [RESIDENTIAL_PHONE], [JENIS_USAHA],
                                                        [ENTRY_BY], [ENTRY_DATE], [UPDATE_BY], [UPDATE_DATE], [PIPELINE_START_TIME], [GU_FUP],
                                                        [PROSPECT_TYPE], [DEBTOR_TYPE], [CHANNELING], [EKSTERNAL], [REFERENSI], [NIP], [ALTERNATE_FOR], [KARAKTER], FLAG_FREEZE)
                                                      VALUES (@PROSPECT_ID, @PROSPECT_NO, @UNIT_CODE, @SOURCE_CODE, @AO_CODE, @PROGRESS_STATUS, @CIF_NUMBER, 
                                                        @LOAN_NUMBER, @FULL_NAME, @PRODUCT_CODE, @CURRENT_OS, 
                                                        @CURRENT_INSTALLMENT, @RENCANA_PLAFON, @OFFER_PLAFON,
                                                        @OFFER_INSTALLMENT, @OFFER_INTEREST, @OFFER_TENOR, 
                                                        @OFFER_FRESH_MONEY, @PRIORITY, @KODYA_KAB_BUSINESS,
                                                        @KECAMATAN_BUSINESS, @KELURAHAN_BUSINESS, @ZIP_CODE_BUSINESS,
                                                        @ADDRESS_BUSINESS, @MOBILE_PHONE, @OFFICE_PHONE, @RESIDENTIAL_PHONE, @JENIS_USAHA,
                                                        @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @PIPELINE_START_TIME, @GU_FUP,
                                                        @PROSPECT_TYPE, @DEBTOR_TYPE, @CHANNELING, @EKSTERNAL, @REFERENSI, @NIP, @ALTERNATE_FOR, @KARAKTER, @FLAG_FREEZE)");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID.Trim().ToUpper());
                        db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, prospect.PROSPECT_NO);
                        db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, prospect.UNIT_CODE);
                        db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, prospect.SOURCE_CODE);
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, prospect.AO_CODE);
                        db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, prospect.PROGRESS_STATUS);
                        db.AddInParameter(cmd, "@FULL_NAME", DbType.String, prospect.FULL_NAME);
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32, prospect.CIF_NUMBER);
                        db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, prospect.LOAN_NUMBER);
                        db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, prospect.PRODUCT_CODE);
                        db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, prospect.CURRENT_OS);
                        db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, prospect.CURRENT_INSTALLMENT);
                        db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, prospect.RENCANA_PLAFON);
                        db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, prospect.OFFER_PLAFON);
                        db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, prospect.OFFER_INSTALLMENT);
                        db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, prospect.OFFER_INTEREST);
                        db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, prospect.OFFER_TENOR);
                        db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, prospect.OFFER_FRESH_MONEY);
                        db.AddInParameter(cmd, "@PRIORITY", DbType.String, prospect.PRIORITY);
                        db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, prospect.KODYA_KAB_BUSINESS);
                        db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, prospect.KECAMATAN_BUSINESS);
                        db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, prospect.KELURAHAN_BUSINESS);
                        db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, prospect.ZIP_CODE_BUSINESS);
                        db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, prospect.ADDRESS_BUSINESS);
                        db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, prospect.MOBILE_PHONE);
                        db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, prospect.OFFICE_PHONE);
                        db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, prospect.RESIDENTIAL_PHONE);
                        db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, prospect.JENIS_USAHA);
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, prospect.ENTRY_BY);
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                        db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, prospect.ENTRY_DATE);
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, prospect.ENTRY_BY);
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                        db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, prospect.GU_FUP);
                        db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, prospect.PROSPECT_TYPE);
                        db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, prospect.DEBTOR_TYPE);
                        db.AddInParameter(cmd, "@CHANNELING", DbType.String, prospect.CHANNELING);
                        db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, prospect.EKSTERNAL);
                        db.AddInParameter(cmd, "@REFERENSI", DbType.String, prospect.REFERENSI);
                        db.AddInParameter(cmd, "@NIP", DbType.String, prospect.ENTRY_BY);
                        db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, prospect.ALTERNATE_FOR);
                        db.AddInParameter(cmd, "@KARAKTER", DbType.String, "2");

                        db.AddInParameter(cmd, "@FLAG_FREEZE", DbType.Int16, prospect.FLAG_FREEZE);
                        if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                        {
                            cmd = db.GetSqlStringCommand(@"IF NOT EXISTS (SELECT * FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID) INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE) VALUES (@PROSPECT_ID, @UPDATE_DATE)");
                            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID);
                            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.String, prospect.ENTRY_DATE);
                            l_result = db.ExecuteNonQuery(cmd, dbTran);
                        }
                    }
                }
                if (l_result > 0) dbTran.Commit();
                else dbTran.Rollback();
            }
            catch (Exception)
            {
                dbTran.Rollback();
            }
            finally
            {
                if (connection.State == ConnectionState.Open) connection.Close();
            }
            return l_result;
        }

        private DateTime ProcessTime(DateTime ProcesingDate)
        {
            return new DateTime(ProcesingDate.Year, ProcesingDate.Month, ProcesingDate.Day, DateTime.Now.Hour,
                DateTime.Now.Minute, DateTime.Now.Second, DateTime.Now.Millisecond, ProcesingDate.Kind);
        }

        bool ValidateDataSetEmpty(DataSet vDataSet)
        {
            if (vDataSet != null)
            {
                if (vDataSet.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
            }
            return false;
        }

        private DateTime AddWorkDays(DateTime date, int workingDays, List<DateTime> Holidays)
        {
            int direction = workingDays < 0 ? -1 : 1;
            DateTime newDate = date.Date;
            while (workingDays != 0)
            {
                newDate = newDate.AddDays(direction);
                if (newDate.DayOfWeek != DayOfWeek.Saturday &&
                    newDate.DayOfWeek != DayOfWeek.Sunday &&
                    !Holidays.Contains(newDate))
                {
                    workingDays -= direction;
                }
            }
            return newDate;
        }

        public int Approve(string CIF_NUMBER, string LOAN_NUMBER, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("TOPUP_APPROVAL");

            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

            return db.ExecuteNonQuery(cmd);
        }

        public int Reject(string CIF_NUMBER, string LOAN_NUMBER)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                            DELETE FROM TBL_TOPUP_TMP
                            WHERE LTRIM(RTRIM(CIF_NUMBER)) LIKE '' + @CIF_NUMBER + '' AND LTRIM(RTRIM(LOAN_NUMBER)) LIKE '' + @LOAN_NUMBER + '' AND IS_MAIN_DELETED = '1';
                            ");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            return db.ExecuteNonQuery(cmd);
        }

        private bool CharToBool(string source)
        {
            if (source == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        private string BoolToChar(bool source)
        {
            if (source)
            {
                return "1";
            }
            else
            {
                return "0";
            }
        }

        public int Update(TopUpInfo TBL_TOPUP_TMP)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_TOPUP SET IS_DOWNLOADED = '0', IS_ASSIGNED = '0' 
                                WHERE (LTRIM(RTRIM(CIF_NUMBER)) = @CIF_NUMBER AND LTRIM(RTRIM(LOAN_NUMBER)) = @LOAN_NUMBER);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_TOPUP
                                (
                                    CIF_NUMBER,LOAN_NUMBER,UNIT_CODE,DEBITOR_NAME,PRODUCT_CODE,CURRENT_OS,CURRENT_INSTALLMENT,
                                    OFFER_PLAFON,OFFER_INSTALLMENT,OFFER_INTEREST,OFFER_TENOR,OFFER_FRESH_MONEY,
                                    PRIORITY,JENIS_USAHA,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS,KELURAHAN_BUSINESS,ZIP_CODE_BUSINESS,ADDRESS_BUSINESS,
                                    MOBILE_PHONE,OFFICE_PHONE,RESIDENTIAL_PHONE,DATE_VISIT_PLAN,
                                    ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,
                                    PROSPECT_TYPE,DEBTOR_TYPE,CHANNELING
                                ) 
                                VALUES
                                (
                                    LEFT(@CIF_NUMBER,8),LEFT(@LOAN_NUMBER,15),LEFT(@UNIT_CODE,6),LEFT(@DEBITOR_NAME,50),LEFT(@PRODUCT_CODE,5),@CURRENT_OS,@CURRENT_INSTALLMENT,
                                    @OFFER_PLAFON,@OFFER_INSTALLMENT,LEFT(@OFFER_INTEREST,5),@OFFER_TENOR,@OFFER_FRESH_MONEY,
                                    LEFT(@PRIORITY,20),LEFT(@JENIS_USAHA,100), LEFT(@KODYA_KAB_BUSINESS,50),LEFT(@KECAMATAN_BUSINESS,50),LEFT(@KELURAHAN_BUSINESS,50),LEFT(@ZIP_CODE_BUSINESS,6),LEFT(@ADDRESS_BUSINESS,255),
                                    LEFT(@MOBILE_PHONE,18),RIGHT(@OFFICE_PHONE,12),RIGHT(@RESIDENTIAL_PHONE,12),@DATE_VISIT_PLAN,
                                    @ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE,
                                    LEFT(@PROSPECT_TYPE,10),LEFT(@DEBTOR_TYPE,10),LEFT(@CHANNELING,10)
                                );

                                DELETE FROM TBL_TOPUP_DOWNLOAD WHERE CIF_NUMBER = @CIF_NUMBER AND LOAN_NUMBER = @LOAN_NUMBER;
                                INSERT INTO TBL_TOPUP_DOWNLOAD (CIF_NUMBER, LOAN_NUMBER) VALUES (@CIF_NUMBER, @LOAN_NUMBER);
                            ");
                db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, TBL_TOPUP_TMP.CIF_NUMBER);
                db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, TBL_TOPUP_TMP.LOAN_NUMBER);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, TBL_TOPUP_TMP.UNIT_CODE);
                db.AddInParameter(cmd, "@DEBITOR_NAME", DbType.String, TBL_TOPUP_TMP.DEBITOR_NAME);
                db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, TBL_TOPUP_TMP.PRODUCT_CODE);
                db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, TBL_TOPUP_TMP.CURRENT_OS);
                db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, TBL_TOPUP_TMP.CURRENT_INSTALLMENT);
                db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, TBL_TOPUP_TMP.OFFER_PLAFON);
                db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, TBL_TOPUP_TMP.OFFER_INSTALLMENT);
                db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, TBL_TOPUP_TMP.OFFER_INTEREST);
                db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, TBL_TOPUP_TMP.OFFER_TENOR);
                db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, TBL_TOPUP_TMP.OFFER_FRESH_MONEY);
                db.AddInParameter(cmd, "@PRIORITY", DbType.String, TBL_TOPUP_TMP.PRIORITY);
                db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, TBL_TOPUP_TMP.JENIS_USAHA);
                db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, TBL_TOPUP_TMP.KODYA_KAB_BUSINESS);
                db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, TBL_TOPUP_TMP.KECAMATAN_BUSINESS);
                db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, TBL_TOPUP_TMP.KELURAHAN_BUSINESS);
                db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, TBL_TOPUP_TMP.ZIP_CODE_BUSINESS);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, TBL_TOPUP_TMP.ADDRESS_BUSINESS);
                db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, TBL_TOPUP_TMP.MOBILE_PHONE);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, TBL_TOPUP_TMP.OFFICE_PHONE);
                db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, TBL_TOPUP_TMP.RESIDENTIAL_PHONE);
                db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime, TBL_TOPUP_TMP.DATE_VISIT_PLAN);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_TOPUP_TMP.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_TOPUP_TMP.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_TOPUP_TMP.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_TOPUP_TMP.UPDATE_DATE);
                db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, TBL_TOPUP_TMP.PROSPECT_TYPE);
                db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, TBL_TOPUP_TMP.DEBTOR_TYPE);
                db.AddInParameter(cmd, "@CHANNELING", DbType.String, TBL_TOPUP_TMP.CHANNELING);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex) { }
            return status;
        }
    }
}